package source;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

public class Database {

	private static Database instance;
	private Statement stmt = null;
    private Connection conn = null;
    
    
    public static Database getInstance()
    {
    	if(instance == null)
    		instance = new Database();
    	return instance;
    }
    
    private void openConnection()
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            // Costruisco la stringa di connessione
            String connection = "jdbc:mysql://" + Config.getProperty("db_host") + "/"+Config.getProperty("db_name")+"?user=" + Config.getProperty("db_user") + "&password=" + Config.getProperty("db_pass");
            conn = DriverManager.getConnection(connection);
            conn.setAutoCommit(true);
            stmt = conn.createStatement();
		}
        catch (Exception e)
        {
            e.printStackTrace();
		}
    }

    private void closeConnection()
    {
    	try
    	{
    		stmt.close();
            conn.close();
    	}
    	catch(Exception e)
    	{
            
    	}
    }
    
    public ResultSet select(String query)
    {
    	openConnection();
        CachedRowSet crs = null;
        
    	try
    	{
            crs = new CachedRowSetImpl();
            crs.setCommand(query);
            crs.execute(stmt.getConnection());
    	}
    	catch (SQLException e)
		{
            e.printStackTrace();
            return null;
		}
    	finally
    	{
    		closeConnection();
    	}
		return crs;
    }

    public String selectSingleValue(String query)
    {
    	openConnection();
        CachedRowSet crs = null;
        
    	try
    	{
            crs = new CachedRowSetImpl();
            crs.setCommand(query);
            crs.execute(stmt.getConnection());
            if(crs.next())
            	return crs.getString(1);
            else
            	return null;
    	}
    	catch (SQLException e)
		{
            return null;
		}
    	finally
    	{
    		closeConnection();
    	}
    }
    
    public boolean insert(String query)
    {
        openConnection();
        CachedRowSet crs = null;
        
        try
        {
            crs = new CachedRowSetImpl();
            crs.setCommand(query);
            crs.execute(stmt.getConnection());
        }
        catch (SQLException e)
        {
            System.err.println(e.getMessage());
            return false;
        }
        finally
        {
            closeConnection();
        }
        return true;
    }

    public boolean update(String query)
    {
        openConnection();
        CachedRowSet crs = null;
        
        try
        {
            crs = new CachedRowSetImpl();
            crs.setCommand(query);
            crs.execute(stmt.getConnection());
        }
        catch (SQLException e)
        {
            
            return false;
        }
        finally
        {
            closeConnection();
        }
        return true;
    }
    
    public boolean delete(String query)
    {
        openConnection();
        CachedRowSet crs = null;
        
        try
        {
            crs = new CachedRowSetImpl();
            crs.setCommand(query);
            crs.execute(stmt.getConnection());
        }
        catch (SQLException e)
        {
            
            return false;
        }
        finally
        {
            closeConnection();
        }
        return true;
    }
}
